﻿/*
Deployment script for commerce4Umbraco_v1_install
*/

GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;

SET NUMERIC_ROUNDABORT OFF;


PRINT N'Dropping DF_CMRC_ProductAttributes_attributeTypeID...';


GO
ALTER TABLE [dbo].[CSK_Store_Attribute] DROP CONSTRAINT [DF_CMRC_ProductAttributes_attributeTypeID];


GO
PRINT N'Dropping DF_CSK_Store_Category_categoryGUID...';


GO
ALTER TABLE [dbo].[CSK_Store_Category] DROP CONSTRAINT [DF_CSK_Store_Category_categoryGUID];


GO
PRINT N'Dropping DF_CMRC_ProductCategories_parentID...';


GO
ALTER TABLE [dbo].[CSK_Store_Category] DROP CONSTRAINT [DF_CMRC_ProductCategories_parentID];


GO
PRINT N'Dropping DF_CMRC_ProductCategories_listOrder...';


GO
ALTER TABLE [dbo].[CSK_Store_Category] DROP CONSTRAINT [DF_CMRC_ProductCategories_listOrder];


GO
PRINT N'Dropping DF_CSK_Store_Category_createdOn...';


GO
ALTER TABLE [dbo].[CSK_Store_Category] DROP CONSTRAINT [DF_CSK_Store_Category_createdOn];


GO
PRINT N'Dropping DF_CSK_Store_Category_modifiedOn...';


GO
ALTER TABLE [dbo].[CSK_Store_Category] DROP CONSTRAINT [DF_CSK_Store_Category_modifiedOn];


GO
PRINT N'Dropping DF_CSK_Store_Manufacturer_createdOn...';


GO
ALTER TABLE [dbo].[CSK_Store_Manufacturer] DROP CONSTRAINT [DF_CSK_Store_Manufacturer_createdOn];


GO
PRINT N'Dropping DF_CSK_Store_Manufacturer_modifiedOn...';


GO
ALTER TABLE [dbo].[CSK_Store_Manufacturer] DROP CONSTRAINT [DF_CSK_Store_Manufacturer_modifiedOn];


GO
PRINT N'Dropping DF_CMRC_Products_isDeleted...';


GO
ALTER TABLE [dbo].[CSK_Store_Product] DROP CONSTRAINT [DF_CMRC_Products_isDeleted];


GO
PRINT N'Dropping DF_CMRC_Products_listOrder...';


GO
ALTER TABLE [dbo].[CSK_Store_Product] DROP CONSTRAINT [DF_CMRC_Products_listOrder];


GO
PRINT N'Dropping DF_CMRC_Products_RatingSum...';


GO
ALTER TABLE [dbo].[CSK_Store_Product] DROP CONSTRAINT [DF_CMRC_Products_RatingSum];


GO
PRINT N'Dropping DF_CMRC_Products_totalRatingSum...';


GO
ALTER TABLE [dbo].[CSK_Store_Product] DROP CONSTRAINT [DF_CMRC_Products_totalRatingSum];


GO
PRINT N'Dropping DF_CSK_Store_Product_createdOn...';


GO
ALTER TABLE [dbo].[CSK_Store_Product] DROP CONSTRAINT [DF_CSK_Store_Product_createdOn];


GO
PRINT N'Dropping DF_CSK_Store_Product_modifiedOn...';


GO
ALTER TABLE [dbo].[CSK_Store_Product] DROP CONSTRAINT [DF_CSK_Store_Product_modifiedOn];


GO
PRINT N'Dropping DF_CSK_Store_Product_productGUID...';


GO
ALTER TABLE [dbo].[CSK_Store_Product] DROP CONSTRAINT [DF_CSK_Store_Product_productGUID];


GO
PRINT N'Dropping DF_CMRC_Products_manufacturerID...';


GO
ALTER TABLE [dbo].[CSK_Store_Product] DROP CONSTRAINT [DF_CMRC_Products_manufacturerID];


GO
PRINT N'Dropping DF_CMRC_Products_statusID...';


GO
ALTER TABLE [dbo].[CSK_Store_Product] DROP CONSTRAINT [DF_CMRC_Products_statusID];


GO
PRINT N'Dropping DF_CMRC_Products_productTypeID...';


GO
ALTER TABLE [dbo].[CSK_Store_Product] DROP CONSTRAINT [DF_CMRC_Products_productTypeID];


GO
PRINT N'Dropping DF_CMRC_Products_shippingTypeID...';


GO
ALTER TABLE [dbo].[CSK_Store_Product] DROP CONSTRAINT [DF_CMRC_Products_shippingTypeID];


GO
PRINT N'Dropping DF_CMRC_Products_shipTimeID...';


GO
ALTER TABLE [dbo].[CSK_Store_Product] DROP CONSTRAINT [DF_CMRC_Products_shipTimeID];


GO
PRINT N'Dropping DF_CMRC_Products_taxTypeID...';


GO
ALTER TABLE [dbo].[CSK_Store_Product] DROP CONSTRAINT [DF_CMRC_Products_taxTypeID];


GO
PRINT N'Dropping DF_CMRC_Products_weight...';


GO
ALTER TABLE [dbo].[CSK_Store_Product] DROP CONSTRAINT [DF_CMRC_Products_weight];


GO
PRINT N'Dropping DF_CMRC_Products_currencyCode...';


GO
ALTER TABLE [dbo].[CSK_Store_Product] DROP CONSTRAINT [DF_CMRC_Products_currencyCode];


GO
PRINT N'Dropping DF_CMRC_Products_length...';


GO
ALTER TABLE [dbo].[CSK_Store_Product] DROP CONSTRAINT [DF_CMRC_Products_length];


GO
PRINT N'Dropping DF_CMRC_Products_height...';


GO
ALTER TABLE [dbo].[CSK_Store_Product] DROP CONSTRAINT [DF_CMRC_Products_height];


GO
PRINT N'Dropping DF_CMRC_Products_width...';


GO
ALTER TABLE [dbo].[CSK_Store_Product] DROP CONSTRAINT [DF_CMRC_Products_width];


GO
PRINT N'Dropping DF_CMRC_Products_dimensionUnit...';


GO
ALTER TABLE [dbo].[CSK_Store_Product] DROP CONSTRAINT [DF_CMRC_Products_dimensionUnit];


GO
PRINT N'Dropping DF_CMRC_ProductAttributes_priceAdjustment...';


GO
ALTER TABLE [dbo].[CSK_Store_Attribute] DROP CONSTRAINT [DF_CMRC_ProductAttributes_priceAdjustment];


GO
PRINT N'Dropping FK_CMRC_ProductAttributes_CMRC_ProductAttributeTypes...';


GO
ALTER TABLE [dbo].[CSK_Store_Attribute] DROP CONSTRAINT [FK_CMRC_ProductAttributes_CMRC_ProductAttributeTypes];


GO
PRINT N'Dropping FK_CMRC_Products_Categories_CMRC_ProductCategories...';


GO
ALTER TABLE [dbo].[CSK_Store_Product_Category_Map] DROP CONSTRAINT [FK_CMRC_Products_Categories_CMRC_ProductCategories];


GO
PRINT N'Dropping FK_CMRC_Products_CMRC_Manufacturers...';


GO
ALTER TABLE [dbo].[CSK_Store_Product] DROP CONSTRAINT [FK_CMRC_Products_CMRC_Manufacturers];


GO
PRINT N'Dropping FK_CMRC_Products_CMRC_ProductStatus...';


GO
ALTER TABLE [dbo].[CSK_Store_Product] DROP CONSTRAINT [FK_CMRC_Products_CMRC_ProductStatus];


GO
PRINT N'Dropping FK_CMRC_Products_CMRC_ProductType...';


GO
ALTER TABLE [dbo].[CSK_Store_Product] DROP CONSTRAINT [FK_CMRC_Products_CMRC_ProductType];


GO
PRINT N'Dropping FK_CMRC_Products_CMRC_ShippingType...';


GO
ALTER TABLE [dbo].[CSK_Store_Product] DROP CONSTRAINT [FK_CMRC_Products_CMRC_ShippingType];


GO
PRINT N'Dropping FK_CMRC_Products_CMRC_TaxTypes...';


GO
ALTER TABLE [dbo].[CSK_Store_Product] DROP CONSTRAINT [FK_CMRC_Products_CMRC_TaxTypes];


GO
PRINT N'Dropping FK_CMRC_Products_Categories_CMRC_Products...';


GO
ALTER TABLE [dbo].[CSK_Store_Product_Category_Map] DROP CONSTRAINT [FK_CMRC_Products_Categories_CMRC_Products];


GO
PRINT N'Dropping FK_CMRC_Products_Promos_CMRC_Products...';


GO
ALTER TABLE [dbo].[CSK_Promo_Product_Promo_Map] DROP CONSTRAINT [FK_CMRC_Products_Promos_CMRC_Products];


GO
PRINT N'Dropping FK_CMRC_Products_Bundles_CMRC_Products...';


GO
ALTER TABLE [dbo].[CSK_Promo_Product_Bundle_Map] DROP CONSTRAINT [FK_CMRC_Products_Bundles_CMRC_Products];


GO
PRINT N'Dropping FK_CMRC_ProductAttributes_CMRC_Products...';


GO
ALTER TABLE [dbo].[CSK_Store_Attribute] DROP CONSTRAINT [FK_CMRC_ProductAttributes_CMRC_Products];


GO
PRINT N'Dropping FK_CSK_Promo_Product_CrossSell_Map_CSK_Store_Product...';


GO
ALTER TABLE [dbo].[CSK_Promo_Product_CrossSell_Map] DROP CONSTRAINT [FK_CSK_Promo_Product_CrossSell_Map_CSK_Store_Product];


GO
PRINT N'Dropping FK_CSK_Promo_Product_CrossSell_Map_CSK_Store_Product1...';


GO
ALTER TABLE [dbo].[CSK_Promo_Product_CrossSell_Map] DROP CONSTRAINT [FK_CSK_Promo_Product_CrossSell_Map_CSK_Store_Product1];


GO
PRINT N'Dropping FK_CMRC_ProductImages_CMRC_Products...';


GO
ALTER TABLE [dbo].[CSK_Store_Image] DROP CONSTRAINT [FK_CMRC_ProductImages_CMRC_Products];


GO
PRINT N'Dropping FK_CSK_Store_ProductDescriptor_CSK_Store_Product...';


GO
ALTER TABLE [dbo].[CSK_Store_ProductDescriptor] DROP CONSTRAINT [FK_CSK_Store_ProductDescriptor_CSK_Store_Product];


GO
PRINT N'Dropping PK_CMRC_ProductAttributes...';


GO
ALTER TABLE [dbo].[CSK_Store_Attribute] DROP CONSTRAINT [PK_CMRC_ProductAttributes];


GO
PRINT N'Altering [dbo].[CSK_Coupons]...';


GO
ALTER TABLE [dbo].[CSK_Coupons]
    ADD [AutoAppliedCoupon] BIT CONSTRAINT [DF_CSK_Coupons_] DEFAULT ((0)) NOT NULL;


GO
PRINT N'Altering [dbo].[CSK_Store_Attribute]...';


GO
ALTER TABLE [dbo].[CSK_Store_Attribute] DROP COLUMN [attributeName], COLUMN [description], COLUMN [priceAdjustment], COLUMN [selectionList];


GO
ALTER TABLE [dbo].[CSK_Store_Attribute] ALTER COLUMN [attributeTypeID] INT NULL;


GO
ALTER TABLE [dbo].[CSK_Store_Attribute]
    ADD [templateID] INT CONSTRAINT [DF_CSK_Store_Attribute_templateID] DEFAULT ((1)) NOT NULL;


GO
PRINT N'Creating PK_CSK_Store_Attribute...';


GO
ALTER TABLE [dbo].[CSK_Store_Attribute]
    ADD CONSTRAINT [PK_CSK_Store_Attribute] PRIMARY KEY CLUSTERED ([productAttributeID] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
PRINT N'Altering [dbo].[CSK_Store_AttributeTemplate]...';


GO
ALTER TABLE [dbo].[CSK_Store_AttributeTemplate] DROP COLUMN [selectionList];


GO
PRINT N'Starting rebuilding table [dbo].[CSK_Store_Category]...';


GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET XACT_ABORT ON;

BEGIN TRANSACTION;

CREATE TABLE [dbo].[tmp_ms_xx_CSK_Store_Category] (
    [categoryID]       INT              NOT NULL,
    [categoryGUID]     UNIQUEIDENTIFIER CONSTRAINT [DF_CSK_Store_Category_categoryGUID] DEFAULT (newid()) NOT NULL,
    [categoryName]     NVARCHAR (150)   NOT NULL,
    [manufacturerID]   INT              NULL,
    [imageFile]        NVARCHAR (500)   NULL,
    [parentID]         INT              CONSTRAINT [DF_CMRC_ProductCategories_parentID] DEFAULT ((0)) NULL,
    [shortDescription] NVARCHAR (500)   NULL,
    [longDescription]  NTEXT            NULL,
    [listOrder]        INT              CONSTRAINT [DF_CMRC_ProductCategories_listOrder] DEFAULT ((1)) NOT NULL,
    [createdOn]        DATETIME         CONSTRAINT [DF_CSK_Store_Category_createdOn] DEFAULT (getdate()) NULL,
    [createdBy]        NVARCHAR (50)    NULL,
    [modifiedOn]       DATETIME         CONSTRAINT [DF_CSK_Store_Category_modifiedOn] DEFAULT (getdate()) NULL,
    [modifiedBy]       NVARCHAR (50)    NULL,
    [statusID]         INT              NULL,
    [urlFriendly]      NVARCHAR (150)   CONSTRAINT [DF_CSK_Store_Category_urlFriendly] DEFAULT ('') NOT NULL
);

ALTER TABLE [dbo].[tmp_ms_xx_CSK_Store_Category]
    ADD CONSTRAINT [tmp_ms_xx_clusteredindex_PK_CMRC_ProductCategories] PRIMARY KEY CLUSTERED ([categoryID] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);

IF EXISTS (SELECT TOP 1 1
           FROM   [dbo].[CSK_Store_Category])
    BEGIN
        INSERT INTO [dbo].[tmp_ms_xx_CSK_Store_Category] ([categoryID], [categoryGUID], [categoryName], [manufacturerID], [imageFile], [parentID], [shortDescription], [longDescription], [listOrder], [createdOn], [createdBy], [modifiedOn], [modifiedBy])
        SELECT   [categoryID],
                 [categoryGUID],
                 [categoryName],
                 [manufacturerID],
                 [imageFile],
                 [parentID],
                 [shortDescription],
                 [longDescription],
                 [listOrder],
                 [createdOn],
                 [createdBy],
                 [modifiedOn],
                 [modifiedBy]
        FROM     [dbo].[CSK_Store_Category]
        ORDER BY [categoryID] ASC;
    END

DROP TABLE [dbo].[CSK_Store_Category];

EXECUTE sp_rename N'[dbo].[tmp_ms_xx_CSK_Store_Category]', N'CSK_Store_Category';

EXECUTE sp_rename N'[dbo].[tmp_ms_xx_clusteredindex_PK_CMRC_ProductCategories]', N'PK_CMRC_ProductCategories', N'OBJECT';

COMMIT TRANSACTION;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;


GO
PRINT N'Starting rebuilding table [dbo].[CSK_Store_Manufacturer]...';


GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET XACT_ABORT ON;

BEGIN TRANSACTION;

CREATE TABLE [dbo].[tmp_ms_xx_CSK_Store_Manufacturer] (
    [manufacturerID] INT           NOT NULL,
    [manufacturer]   NVARCHAR (50) NOT NULL,
    [createdOn]      DATETIME      CONSTRAINT [DF_CSK_Store_Manufacturer_createdOn] DEFAULT (getdate()) NULL,
    [createdBy]      NVARCHAR (50) NULL,
    [modifiedOn]     DATETIME      CONSTRAINT [DF_CSK_Store_Manufacturer_modifiedOn] DEFAULT (getdate()) NULL,
    [modifiedBy]     NVARCHAR (50) NULL
);

ALTER TABLE [dbo].[tmp_ms_xx_CSK_Store_Manufacturer]
    ADD CONSTRAINT [tmp_ms_xx_clusteredindex_PK_CMRC_Manufacturers] PRIMARY KEY CLUSTERED ([manufacturerID] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);

IF EXISTS (SELECT TOP 1 1
           FROM   [dbo].[CSK_Store_Manufacturer])
    BEGIN
        INSERT INTO [dbo].[tmp_ms_xx_CSK_Store_Manufacturer] ([manufacturerID], [manufacturer], [createdOn], [createdBy], [modifiedOn], [modifiedBy])
        SELECT   [manufacturerID],
                 [manufacturer],
                 [createdOn],
                 [createdBy],
                 [modifiedOn],
                 [modifiedBy]
        FROM     [dbo].[CSK_Store_Manufacturer]
        ORDER BY [manufacturerID] ASC;
    END

DROP TABLE [dbo].[CSK_Store_Manufacturer];

EXECUTE sp_rename N'[dbo].[tmp_ms_xx_CSK_Store_Manufacturer]', N'CSK_Store_Manufacturer';

EXECUTE sp_rename N'[dbo].[tmp_ms_xx_clusteredindex_PK_CMRC_Manufacturers]', N'PK_CMRC_Manufacturers', N'OBJECT';

COMMIT TRANSACTION;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;


GO
PRINT N'Altering [dbo].[CSK_Store_Order]...';


GO
ALTER TABLE [dbo].[CSK_Store_Order]
    ADD [poNumber]        NVARCHAR (50) NULL,
        [receivedPayment] BIT           NULL;


GO
PRINT N'Altering [dbo].[CSK_Store_OrderItem]...';


GO
ALTER TABLE [dbo].[CSK_Store_OrderItem] ALTER COLUMN [sku] NVARCHAR (100) NULL;


GO
PRINT N'Starting rebuilding table [dbo].[CSK_Store_Product]...';


GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET XACT_ABORT ON;

BEGIN TRANSACTION;

CREATE TABLE [dbo].[tmp_ms_xx_CSK_Store_Product] (
    [productID]        INT              NOT NULL,
    [sku]              NVARCHAR (50)    NULL,
    [productGUID]      UNIQUEIDENTIFIER CONSTRAINT [DF_CSK_Store_Product_productGUID] DEFAULT (newid()) NOT NULL,
    [productName]      NVARCHAR (150)   NOT NULL,
    [shortDescription] NVARCHAR (1000)  NULL,
    [manufacturerID]   INT              CONSTRAINT [DF_CMRC_Products_manufacturerID] DEFAULT ((1)) NOT NULL,
    [attributeXML]     NTEXT            NULL,
    [statusID]         INT              CONSTRAINT [DF_CMRC_Products_statusID] DEFAULT ((1)) NOT NULL,
    [productTypeID]    INT              CONSTRAINT [DF_CMRC_Products_productTypeID] DEFAULT ((1)) NOT NULL,
    [shippingTypeID]   INT              CONSTRAINT [DF_CMRC_Products_shippingTypeID] DEFAULT ((1)) NOT NULL,
    [shipEstimateID]   INT              CONSTRAINT [DF_CMRC_Products_shipTimeID] DEFAULT ((1)) NOT NULL,
    [taxTypeID]        INT              CONSTRAINT [DF_CMRC_Products_taxTypeID] DEFAULT ((1)) NOT NULL,
    [stockLocation]    NVARCHAR (150)   NULL,
    [ourPrice]         MONEY            NOT NULL,
    [retailPrice]      MONEY            NOT NULL,
    [weight]           NUMERIC (19, 4)  CONSTRAINT [DF_CMRC_Products_weight] DEFAULT ((0)) NOT NULL,
    [currencyCode]     CHAR (3)         CONSTRAINT [DF_CMRC_Products_currencyCode] DEFAULT ('USD') NOT NULL,
    [unitOfMeasure]    NVARCHAR (50)    NULL,
    [adminComments]    NVARCHAR (1000)  NULL,
    [length]           NUMERIC (18)     CONSTRAINT [DF_CMRC_Products_length] DEFAULT ((0)) NOT NULL,
    [height]           NUMERIC (18)     CONSTRAINT [DF_CMRC_Products_height] DEFAULT ((0)) NOT NULL,
    [width]            NUMERIC (18)     CONSTRAINT [DF_CMRC_Products_width] DEFAULT ((0)) NOT NULL,
    [dimensionUnit]    VARCHAR (20)     CONSTRAINT [DF_CMRC_Products_dimensionUnit] DEFAULT ('inches') NOT NULL,
    [isDeleted]        BIT              CONSTRAINT [DF_CMRC_Products_isDeleted] DEFAULT ((0)) NOT NULL,
    [listOrder]        INT              CONSTRAINT [DF_CMRC_Products_listOrder] DEFAULT ((1)) NOT NULL,
    [ratingSum]        INT              CONSTRAINT [DF_CMRC_Products_RatingSum] DEFAULT ((4)) NOT NULL,
    [totalRatingVotes] INT              CONSTRAINT [DF_CMRC_Products_totalRatingSum] DEFAULT ((1)) NOT NULL,
    [defaultImage]     NVARCHAR (500)   NULL,
    [createdOn]        DATETIME         CONSTRAINT [DF_CSK_Store_Product_createdOn] DEFAULT (getdate()) NULL,
    [createdBy]        NVARCHAR (50)    NULL,
    [modifiedOn]       DATETIME         CONSTRAINT [DF_CSK_Store_Product_modifiedOn] DEFAULT (getdate()) NULL,
    [modifiedBy]       NVARCHAR (50)    NULL,
    [inventory]        INT              NULL,
    [urlFriendly]      NVARCHAR (150)   CONSTRAINT [DF_CSK_Store_Product_urlFriendly] DEFAULT ('') NOT NULL
);

ALTER TABLE [dbo].[tmp_ms_xx_CSK_Store_Product]
    ADD CONSTRAINT [tmp_ms_xx_clusteredindex_PK_CMRC_Products] PRIMARY KEY CLUSTERED ([productID] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);

IF EXISTS (SELECT TOP 1 1
           FROM   [dbo].[CSK_Store_Product])
    BEGIN
        INSERT INTO [dbo].[tmp_ms_xx_CSK_Store_Product] ([productID], [sku], [productGUID], [productName], [shortDescription], [manufacturerID], [attributeXML], [statusID], [productTypeID], [shippingTypeID], [shipEstimateID], [taxTypeID], [stockLocation], [ourPrice], [retailPrice], [weight], [currencyCode], [unitOfMeasure], [adminComments], [length], [height], [width], [dimensionUnit], [isDeleted], [listOrder], [ratingSum], [totalRatingVotes], [defaultImage], [createdOn], [createdBy], [modifiedOn], [modifiedBy])
        SELECT   [productID],
                 [sku],
                 [productGUID],
                 [productName],
                 [shortDescription],
                 [manufacturerID],
                 [attributeXML],
                 [statusID],
                 [productTypeID],
                 [shippingTypeID],
                 [shipEstimateID],
                 [taxTypeID],
                 [stockLocation],
                 [ourPrice],
                 [retailPrice],
                 [weight],
                 [currencyCode],
                 [unitOfMeasure],
                 [adminComments],
                 [length],
                 [height],
                 [width],
                 [dimensionUnit],
                 [isDeleted],
                 [listOrder],
                 [ratingSum],
                 [totalRatingVotes],
                 [defaultImage],
                 [createdOn],
                 [createdBy],
                 [modifiedOn],
                 [modifiedBy]
        FROM     [dbo].[CSK_Store_Product]
        ORDER BY [productID] ASC;
    END

DROP TABLE [dbo].[CSK_Store_Product];

EXECUTE sp_rename N'[dbo].[tmp_ms_xx_CSK_Store_Product]', N'CSK_Store_Product';

EXECUTE sp_rename N'[dbo].[tmp_ms_xx_clusteredindex_PK_CMRC_Products]', N'PK_CMRC_Products', N'OBJECT';

COMMIT TRANSACTION;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;


GO
PRINT N'Creating [dbo].[CSK_Store_AttributeItem]...';


GO
CREATE TABLE [dbo].[CSK_Store_AttributeItem] (
    [AttributeItemId] INT           IDENTITY (1, 1) NOT NULL,
    [templateId]      INT           NOT NULL,
    [Name]            NVARCHAR (50) NOT NULL,
    [Adjustment]      MONEY         NOT NULL,
    [SortOrder]       INT           NOT NULL,
    [SkuSuffix]       NVARCHAR (50) NULL,
    [createdOn]       DATETIME      NULL,
    [createdBy]       NVARCHAR (50) NULL,
    [modifiedOn]      DATETIME      NULL,
    [modifiedBy]      NVARCHAR (50) NULL
);


GO
PRINT N'Creating PK_CSK_Store_AttributeItem...';


GO
ALTER TABLE [dbo].[CSK_Store_AttributeItem]
    ADD CONSTRAINT [PK_CSK_Store_AttributeItem] PRIMARY KEY CLUSTERED ([AttributeItemId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
PRINT N'Creating [dbo].[CSK_Store_OrderInstruction]...';


GO
CREATE TABLE [dbo].[CSK_Store_OrderInstruction] (
    [instructionID] INT             IDENTITY (1, 1) NOT NULL,
    [orderID]       INT             NOT NULL,
    [note]          NVARCHAR (1500) NOT NULL,
    [isGift]        BIT             NOT NULL,
    [createdOn]     DATETIME        NULL,
    [createdBy]     NVARCHAR (50)   NULL,
    [modifiedOn]    DATETIME        NULL,
    [modifiedBy]    NVARCHAR (50)   NULL
);


GO
PRINT N'Creating PK_CMRC_OrderInstruction...';


GO
ALTER TABLE [dbo].[CSK_Store_OrderInstruction]
    ADD CONSTRAINT [PK_CMRC_OrderInstruction] PRIMARY KEY CLUSTERED ([instructionID] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
PRINT N'Creating DF_CSK_Store_AttributeItem_createdOn...';


GO
ALTER TABLE [dbo].[CSK_Store_AttributeItem]
    ADD CONSTRAINT [DF_CSK_Store_AttributeItem_createdOn] DEFAULT (getdate()) FOR [createdOn];


GO
PRINT N'Creating DF_CSK_Store_AttributeItem_modifiedOn...';


GO
ALTER TABLE [dbo].[CSK_Store_AttributeItem]
    ADD CONSTRAINT [DF_CSK_Store_AttributeItem_modifiedOn] DEFAULT (getdate()) FOR [modifiedOn];


GO
PRINT N'Creating FK_CMRC_Products_Categories_CMRC_ProductCategories...';


GO
ALTER TABLE [dbo].[CSK_Store_Product_Category_Map] WITH NOCHECK
    ADD CONSTRAINT [FK_CMRC_Products_Categories_CMRC_ProductCategories] FOREIGN KEY ([categoryID]) REFERENCES [dbo].[CSK_Store_Category] ([categoryID]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
PRINT N'Creating FK_CMRC_Products_CMRC_Manufacturers...';


GO
ALTER TABLE [dbo].[CSK_Store_Product] WITH NOCHECK
    ADD CONSTRAINT [FK_CMRC_Products_CMRC_Manufacturers] FOREIGN KEY ([manufacturerID]) REFERENCES [dbo].[CSK_Store_Manufacturer] ([manufacturerID]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
PRINT N'Creating FK_CMRC_Products_CMRC_ProductStatus...';


GO
ALTER TABLE [dbo].[CSK_Store_Product] WITH NOCHECK
    ADD CONSTRAINT [FK_CMRC_Products_CMRC_ProductStatus] FOREIGN KEY ([statusID]) REFERENCES [dbo].[CSK_Store_ProductStatus] ([statusID]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
PRINT N'Creating FK_CMRC_Products_CMRC_ProductType...';


GO
ALTER TABLE [dbo].[CSK_Store_Product] WITH NOCHECK
    ADD CONSTRAINT [FK_CMRC_Products_CMRC_ProductType] FOREIGN KEY ([productTypeID]) REFERENCES [dbo].[CSK_Store_ProductType] ([productTypeID]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
PRINT N'Creating FK_CMRC_Products_CMRC_ShippingType...';


GO
ALTER TABLE [dbo].[CSK_Store_Product] WITH NOCHECK
    ADD CONSTRAINT [FK_CMRC_Products_CMRC_ShippingType] FOREIGN KEY ([shippingTypeID]) REFERENCES [dbo].[CSK_Store_ShippingType] ([shippingTypeID]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
PRINT N'Creating FK_CMRC_Products_CMRC_TaxTypes...';


GO
ALTER TABLE [dbo].[CSK_Store_Product] WITH NOCHECK
    ADD CONSTRAINT [FK_CMRC_Products_CMRC_TaxTypes] FOREIGN KEY ([taxTypeID]) REFERENCES [dbo].[CSK_Tax_Type] ([taxTypeID]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
PRINT N'Creating FK_CMRC_Products_Categories_CMRC_Products...';


GO
ALTER TABLE [dbo].[CSK_Store_Product_Category_Map] WITH NOCHECK
    ADD CONSTRAINT [FK_CMRC_Products_Categories_CMRC_Products] FOREIGN KEY ([productID]) REFERENCES [dbo].[CSK_Store_Product] ([productID]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
PRINT N'Creating FK_CMRC_Products_Promos_CMRC_Products...';


GO
ALTER TABLE [dbo].[CSK_Promo_Product_Promo_Map] WITH NOCHECK
    ADD CONSTRAINT [FK_CMRC_Products_Promos_CMRC_Products] FOREIGN KEY ([productID]) REFERENCES [dbo].[CSK_Store_Product] ([productID]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
PRINT N'Creating FK_CMRC_Products_Bundles_CMRC_Products...';


GO
ALTER TABLE [dbo].[CSK_Promo_Product_Bundle_Map] WITH NOCHECK
    ADD CONSTRAINT [FK_CMRC_Products_Bundles_CMRC_Products] FOREIGN KEY ([productID]) REFERENCES [dbo].[CSK_Store_Product] ([productID]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
PRINT N'Creating FK_CMRC_ProductAttributes_CMRC_Products...';


GO
ALTER TABLE [dbo].[CSK_Store_Attribute] WITH NOCHECK
    ADD CONSTRAINT [FK_CMRC_ProductAttributes_CMRC_Products] FOREIGN KEY ([productID]) REFERENCES [dbo].[CSK_Store_Product] ([productID]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
PRINT N'Creating FK_CSK_Promo_Product_CrossSell_Map_CSK_Store_Product...';


GO
ALTER TABLE [dbo].[CSK_Promo_Product_CrossSell_Map] WITH NOCHECK
    ADD CONSTRAINT [FK_CSK_Promo_Product_CrossSell_Map_CSK_Store_Product] FOREIGN KEY ([productID]) REFERENCES [dbo].[CSK_Store_Product] ([productID]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
PRINT N'Creating FK_CSK_Promo_Product_CrossSell_Map_CSK_Store_Product1...';


GO
ALTER TABLE [dbo].[CSK_Promo_Product_CrossSell_Map] WITH NOCHECK
    ADD CONSTRAINT [FK_CSK_Promo_Product_CrossSell_Map_CSK_Store_Product1] FOREIGN KEY ([crossProductID]) REFERENCES [dbo].[CSK_Store_Product] ([productID]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
PRINT N'Creating FK_CMRC_ProductImages_CMRC_Products...';


GO
ALTER TABLE [dbo].[CSK_Store_Image] WITH NOCHECK
    ADD CONSTRAINT [FK_CMRC_ProductImages_CMRC_Products] FOREIGN KEY ([productID]) REFERENCES [dbo].[CSK_Store_Product] ([productID]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
PRINT N'Creating FK_CSK_Store_ProductDescriptor_CSK_Store_Product...';


GO
ALTER TABLE [dbo].[CSK_Store_ProductDescriptor] WITH NOCHECK
    ADD CONSTRAINT [FK_CSK_Store_ProductDescriptor_CSK_Store_Product] FOREIGN KEY ([productID]) REFERENCES [dbo].[CSK_Store_Product] ([productID]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
PRINT N'Creating FK_CSK_Store_AttributeItem_CSK_Store_AttributeTemplate...';


GO
ALTER TABLE [dbo].[CSK_Store_AttributeItem] WITH NOCHECK
    ADD CONSTRAINT [FK_CSK_Store_AttributeItem_CSK_Store_AttributeTemplate] FOREIGN KEY ([templateId]) REFERENCES [dbo].[CSK_Store_AttributeTemplate] ([templateID]) ON DELETE CASCADE ON UPDATE NO ACTION;


GO
PRINT N'Creating FK_CSK_Store_OrderInstruction_CSK_Store_Order...';


GO
ALTER TABLE [dbo].[CSK_Store_OrderInstruction] WITH NOCHECK
    ADD CONSTRAINT [FK_CSK_Store_OrderInstruction_CSK_Store_Order] FOREIGN KEY ([orderID]) REFERENCES [dbo].[CSK_Store_Order] ([orderID]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
PRINT N'Creating FK_CSK_Store_Attribute_CSK_Store_AttributeTemplate...';


GO
ALTER TABLE [dbo].[CSK_Store_Attribute] WITH NOCHECK
    ADD CONSTRAINT [FK_CSK_Store_Attribute_CSK_Store_AttributeTemplate] FOREIGN KEY ([templateID]) REFERENCES [dbo].[CSK_Store_AttributeTemplate] ([templateID]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
PRINT N'Altering [dbo].[CSK_Store_AddItemToCart]...';


GO


ALTER PROCEDURE [dbo].[CSK_Store_AddItemToCart]
	(
	@userName nvarchar(50),
	@productID int,
	@attributes nvarchar(4000)='',
	@pricePaid money,
	@promoCode nvarchar(50)='',
	@quantity int =1,
	@sku nvarchar(100)=''
	)
AS
	
	--first, get the order ID
	declare @orderID int
	SELECT @orderID=orderID FROM CSK_Store_Order WHERE userName=@userName AND orderStatusID=9999
	
	--reset any discounts/coupons (basket-wide) they don't apply anymore since we're
	--adding an item
	UPDATE CSK_Store_Order SET DiscountAmount=0, CouponCodes='' WHERE orderID=@orderID
	
	
	--reset isLastAdded
	UPDATE CSK_Store_OrderItem SET isLastAdded=0 WHERE orderID=@orderID
	
	--next, see if the product is in the basket, with the same set of attributes
	IF EXISTS (SELECT productID FROM CSK_Store_OrderItem WHERE orderID=@orderID AND productID=@productID AND attributes=@attributes AND sku=@sku)
		BEGIN
			--update the quantity
			--first reset the last item added
			UPDATE CSK_Store_OrderItem 
			SET quantity=quantity+@quantity,isLAstAdded=1
			WHERE orderID=@orderID AND productID=@productID AND attributes=@attributes
		END
	
	ELSE
		BEGIN
			--the product's not in there, add it
			INSERT INTO CSK_Store_OrderItem
          (
          productID, 
          sku, 
          productName, 
          orderID, 
          imageFile, 
          productDescription, 
          promoCode, 
          quantity, 
          originalPrice, 
          pricePaid, 
          attributes, 
          shippingEstimate, 
          rating, 
          isLastAdded,
          createdOn, 
          modifiedOn,
          createdBy,
          modifiedBy,
          weight,
		      length,
		      width,
		      height,
		      dimensionUnit
          )
			SELECT     
			productID, 
			@sku, 
			productName, 
			@orderID, 
			defaultImage, 
			shortDescription, 
			@promoCode, 
			@quantity, 
			retailPrice, 
			@pricePaid, 
			@attributes, 
			shippingEstimate, 
			rating, 
			1,
			getdate(),
			getdate(),
			@userName,
			@userName,
			weight,
			length,
			width,
			height,
			dimensionUnit
			FROM         
			vwProduct
			WHERE 
			productID=@productID
	
		END
	
	
	
	RETURN
GO
PRINT N'Creating [dbo].[OrderCSVDump]...';


GO

-- =============================================
-- Author:		Gary prothero
-- Create date: 12/07/2010
-- Description:	Used for CSV DataDump
-- =============================================
CREATE PROCEDURE [dbo].[OrderCSVDump] 
		@dateStart DateTime,
		@dateEnd DateTime
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	SELECT     CSK_Store_Order.orderNumber, CSK_Store_Order.orderDate, CSK_Store_Order.userName, CSK_Store_Order.email, CSK_Store_Order.firstName, 
						  CSK_Store_Order.lastName, CSK_Store_Order.shipPhone, CSK_Store_Order.shippingMethod, CSK_Store_Order.subTotalAmount, CSK_Store_Order.shippingAmount, 
						  CSK_Store_Order.handlingAmount, CSK_Store_Order.taxAmount, CSK_Store_Order.taxRate, CSK_Store_Order.couponCodes, CSK_Store_Order.discountAmount, 
						  CSK_Store_Order.specialInstructions, CSK_Store_Order.shippingTrackingNumber, CSK_Store_Order.numberOfPackages, CSK_Store_Order.packagingNotes, 
						  CSK_Store_OrderItem.orderItemID, CSK_Store_OrderItem.sku, CSK_Store_OrderItem.productName, CSK_Store_OrderItem.quantity, CSK_Store_OrderItem.originalPrice, 
						  CSK_Store_OrderItem.pricePaid, CSK_Store_OrderItem.attributes, CSK_Store_OrderItem.shippingEstimate, CSK_Store_OrderItem.shipmentReference, 
						  CSK_Store_OrderItem.rating, CSK_Store_OrderItem.isLastAdded, CSK_Store_OrderStatus.OrderStatus, CSK_Store_Product.productTypeID, 
						  CSK_Store_Product.shippingTypeID, CSK_Store_Product.shipEstimateID, CSK_Store_Product.taxTypeID, CSK_Store_Product.stockLocation, 
						  CSK_Store_Product.ourPrice, CSK_Store_Product.retailPrice, CSK_Store_Product.statusID, CSK_Store_OrderItem.shipDate, CSK_Tax_Type.taxType, 
						  CSK_Tax_Type.taxCode, CSK_Tax_Type.isExempt, CSK_Store_Manufacturer.manufacturer
	FROM         CSK_Store_Order INNER JOIN
						  CSK_Store_OrderItem ON CSK_Store_Order.orderID = CSK_Store_OrderItem.orderID INNER JOIN
						  CSK_Store_OrderStatus ON CSK_Store_Order.orderStatusID = CSK_Store_OrderStatus.OrderStatusID INNER JOIN
						  CSK_Store_Product ON CSK_Store_OrderItem.productID = CSK_Store_Product.productID INNER JOIN
						  CSK_Tax_Type ON CSK_Store_Product.taxTypeID = CSK_Tax_Type.taxTypeID INNER JOIN
						  CSK_Store_Manufacturer ON CSK_Store_Product.manufacturerID = CSK_Store_Manufacturer.manufacturerID
	WHERE CSK_Store_Order.orderDate >= @dateStart AND CSK_Store_Order.orderDate <= @dateEnd
END
GO
PRINT N'Refreshing [dbo].[vCoupons]...';


GO
EXECUTE sp_refreshview N'dbo.vCoupons';


GO
PRINT N'Refreshing [dbo].[vwProduct]...';


GO
EXECUTE sp_refreshview N'dbo.vwProduct';


GO
PRINT N'Refreshing [dbo].[vwProductSearch]...';


GO
EXECUTE sp_refreshview N'dbo.vwProductSearch';


GO
PRINT N'Refreshing [dbo].[vwOrders]...';


GO
EXECUTE sp_refreshview N'dbo.vwOrders';


GO
PRINT N'Refreshing [dbo].[ProductCrossSells]...';


GO
EXECUTE sp_refreshview N'dbo.ProductCrossSells';


GO

